OpenEdge Reporting:<br />Query/Results for UNIX Using Expert mode in WHERE clauses

Unlike the Query option, the Where option lets you define more than one set of criteria for the same field. That is, you can get a set of records that have a customer number less than 10 or greater than 40.

This section provides guidelines for using Expert mode to build and edit WHERE clauses. If you are an experienced Progress 4GL programmer, you might prefer to build WHERE clauses using Expert mode. In Expert mode you enter selection criteria directly without having to go through the set of selection lists. It also enables you to change the default order of evaluation by grouping comparisons with parentheses. (For information about evaluating expressions, see Appendix F "Order of Evaluation.")

You can use Expert mode to build or edit WHERE clauses in the Query, Reports, Data Exports, and Labels modules. However, do not use Expert mode to define or edit WHERE clauses that use Ask mode. For example, if you edit a WHERE clause that uses Ask mode, error messages appear in the Expression window and you cannot continue without deleting the WHERE clause or returning the WHERE clause to its original condition.

You can access Expert mode from anywhere within the WHERE clause builder windows by pressing GET. If you want to edit an existing WHERE clause, you must enter Expert mode from the Choose a Field window. Otherwise, you overwrite the existing WHERE clause when you start to build a new clause using the selection windows.

Select the Where option from the Query main menu.

The windows that help you to build the WHERE clause appears. If you already have a WHERE clause defined, it appears in the Expression window. You use Expert mode to edit the WHERE clause. (You can also delete the WHERE clause or define a new WHERE clause to override the existing one. For a description of how to delete a WHERE clause, see Chapter 3 "A Tour Through Results" for instructions on how to overwrite one, simply define a new WHERE clause.)

Press GET to access Expert mode.

The cursor jumps to the Expression window, to let you bypass the other windows and enter a WHERE clause directly. For example, the following figure shows a previously defined WHERE clause:

Now you can type or edit the WHERE clause criteria you want. You can add a field without typing it in by pressing GET and selecting it from the list of available fields that appears.

Press MODE to toggle to the Insert mode, so that you do not type over existing information.

Edit the existing WHERE clause, or enter the one you want.

Results automatically enters the word WHERE in the clauses. If you enter it yourself, you will get error messages in the Expert mode window when you try to run the WHERE clause. You do not have to capitalize the field names or the operators, but you must prefix each field name with the name of the database and file that contains it. (You do not have to include the database name unless you started Results with more than one database. If you did, specify the database that contains the field you enter. For more information about using Results with multiple databases, see Chapter 8, " Multiple Databases with Results.")

Note: Press GET to access Expert Mode from anywhere within the WHERE clause builder windows. Expert mode also lets you change the default order of evaluation by grouping comparisons with parentheses.

You can use any Progress function in this clause. For example, you can define the following WHERE clauses:

With only the customer file selected, enter this clause to select all customers without orders:


NOT CAN-FIND(FIRST order OF customer)

With only the customer file selected, enter the following clause to select all customers in New England:


LOOKUP(customer.st,"ME,NH,VT,RI,CT,MA") > 0

For information about Progress 4GL functions, see the OpenEdge Development: Progress 4GL Reference manual. For information about evaluating expressions, see Appendix F, "Order of Evaluation," in this manual.

Press GO to apply the WHERE clause.

If you try to run an invalid WHERE clause, you get an error message and the cursor returns to the Expert Mode window. If you get an error message, edit the WHERE clause in the window using the correct format. Because each line of the error messages begins with two asterisks (**), Results ignores them when you run the edited WHERE clause. Therefore, you do not have to delete them.